﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace 软构大作业
{
    class Data
    {
        private SqlConnection sc;

        // 添加构造函数以初始化连接
        public Data()
        {
            sc = new SqlConnection();
        }

        public SqlConnection connect()
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true"; // 数据库连接字符串
            sc.ConnectionString = str;
            sc.Open();
            return sc;
        }

        public SqlCommand command(string sql)
        {
            if (sc.State != System.Data.ConnectionState.Open)
            {
                connect();
            }
            SqlCommand cmd = new SqlCommand(sql, sc);
            return cmd;
        }

        public int Excute(string sql)
        {
            if (sc.State != System.Data.ConnectionState.Open)
            {
                connect();
            }
            return command(sql).ExecuteNonQuery();
        }

        public SqlDataReader read(string sql)
        {
            if (sc.State != System.Data.ConnectionState.Open)
            {
                connect();
            }
            return command(sql).ExecuteReader();
        }

        public void DaoClose()
        {
            if (sc != null && sc.State == System.Data.ConnectionState.Open)
            {
                sc.Close();
            }
        }

        public int ExecuteSql(string sql, SqlParameter[] parameters)
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true";
            using (SqlConnection connection = new SqlConnection(str))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    return command.ExecuteNonQuery();
                }
            }
        }
        public IDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true";//数据库连接字符串
            SqlConnection connection = new SqlConnection(str);
            SqlCommand command = new SqlCommand(sql, connection);

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            connection.Open();
            // CommandBehavior.CloseConnection 表示当reader关闭时，自动关闭连接
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public static DataTable ExecuteQuery(string sql, params SqlParameter[] parameters)
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true";//数据库连接字符串
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(str))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(dt);
                }
            }
            return dt;
        }
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true";//数据库连接字符串
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parameters != null) cmd.Parameters.AddRange(parameters);

                return cmd.ExecuteNonQuery(); // 返回受影响的行数（int）
            }
        }
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            string str = "Server=localhost;Database=library_2;Trusted_Connection=true"; // 数据库连接字符串
            using (SqlConnection connection = new SqlConnection(str))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    connection.Open();
                    return command.ExecuteScalar();
                }
            }
        }
    }
}